package dao

import (
	"context"
	"fmt"
	"github.com/google/uuid"
	"gorm.io/gorm"
)

type ExperimentDAO struct {
	db *gorm.DB
}

func NewExperimentDap(db *gorm.DB) *ExperimentDAO {
	return &ExperimentDAO{
		db: db,
	}
}

func (dao *ExperimentDAO) Query(ctx context.Context, a Experiment) ([]Experiment, error) {
	var Experiments []Experiment
	db := dao.db

	if name := a.Name; name != "" {
		name = fmt.Sprintf("%%%v%%", name)
		db = db.WithContext(ctx).Where("name like ?", name)
	}
	//if a.Name == "" {
	//	return []Experiment{}, nil
	//}
	err := db.Table("experiment").Debug().WithContext(ctx).Find(&Experiments).Error
	if err != nil {
		fmt.Println("query error")
	}
	return Experiments, nil
}

func (dao *ExperimentDAO) QueryModuleTest(ctx context.Context, ModuleId string) ([]Question, error) {
	var Questions []Question
	db := dao.db

	if id := ModuleId; id != "" {
		//condition := fmt.Sprintf("%%%v%%", id)
		db = db.WithContext(ctx).Where("id = ?", id).Order("sort_number")
	}
	if ModuleId == "" {
		return []Question{}, nil
	}
	err := db.Table("question").Debug().WithContext(ctx).Find(&Questions).Error
	if err != nil {
		fmt.Println("query error")
	}
	return Questions, nil
}

func (dao *ExperimentDAO) Delete(ctx context.Context, ids []string) (int64, error) {
	db := dao.db

	if len(ids) == 0 {
		fmt.Println("params error")
		return 0, nil
	}

	err := db.Table("experiment").Debug().WithContext(ctx).Delete(&Experiment{}, ids)
	if err != nil {
		fmt.Println("delete error")
	}

	// todo: delete related experiment items
	err = db.Table("experiment_item").Debug().WithContext(ctx).Where("experiment_id in ?", ids).Delete(&ExperimentItem{})
	if err != nil {
		fmt.Println("delete related items error")
	}

	return err.RowsAffected, nil
}

func (dao *ExperimentDAO) Create(ctx context.Context, e Experiment) (int64, error) {
	db := dao.db
	experimentUUid := uuid.New().String()

	// insert experiment
	experiment := Experiment{
		Id:            experimentUUid,
		Name:          e.Name,
		NameEn:        e.NameEn,
		IfEnabled:     e.IfEnabled,
		Description:   e.Description,
		DescriptionEn: e.DescriptionEn,
	}
	result := db.Table("experiment").Create(&experiment)
	if result.RowsAffected < 1 {
		panic(result.Error)
	}
	var ExperimentItems []ExperimentItem

	// todo: 物品类型硬编码 后续应改为代码集
	if len(e.Equipments) != 0 || len(e.Consumables) != 0 || len(e.Consumables) != 0 {

		for _, v := range e.Equipments {
			ExperimentItems = append(ExperimentItems, ExperimentItem{
				Id:           uuid.New().String(),
				ExperimentId: experimentUUid,
				ItemId:       v,
				ItemType:     "1",
			})
		}

		for _, v := range e.Consumables {
			ExperimentItems = append(ExperimentItems, ExperimentItem{
				Id:           uuid.New().String(),
				ExperimentId: experimentUUid,
				ItemId:       v,
				ItemType:     "2",
			})
		}

		for _, v := range e.Chemicals {
			ExperimentItems = append(ExperimentItems, ExperimentItem{
				Id:           uuid.New().String(),
				ExperimentId: experimentUUid,
				ItemId:       v,
				ItemType:     "3",
			})
		}

		// insert chemical
		err := db.Table("experiment_item").Create(&ExperimentItems).Error

		if err != nil {
			panic(err)
		}
	}

	return result.RowsAffected, nil
}

func (dao *ExperimentDAO) QueryRelatedModule(ctx context.Context, e Experiment) ([]CommonItem, error) {
	//var ExperimentItems []ExperimentItem
	var CommonItems []CommonItem
	//var equipmentItems, consumableItems, chemicalItems []CommonItem
	db := dao.db

	var id = e.Id
	if id == "" {
		return []CommonItem{}, nil
	} else {
		db = db.WithContext(ctx).Where("item.experiment_id = ?", id)
	}

	// 查处对应物品id ，分类
	//err := db.Table("experiment_item").Debug().WithContext(ctx).Select("item_id", "item_type").Where("experiment_id = ?", id).Find(&ExperimentItems).Error
	//if err != nil {
	//	fmt.Println("query error")
	//}

	// 直接查模块表
	//moduleDb := dao.db
	//err = moduleDb.Table("experiment_item").Debug().WithContext(ctx).Where("id = ?", id).Order("item_type").Find(&CommonItems).Error
	//if err != nil {
	//	fmt.Println("query error")
	//}

	//err := db.Debug().
	//	Raw("select distinct module.id, category.name as 'category_name', module.name as 'module_name' from  experiment_item item join safety_learning_module module on item.item_id = module.id join safety_learning_category category on module.category = category.code").
	//	Scan(&CommonItems).Error

	err := db.Debug().
		Table("experiment_item AS item").
		Select("DISTINCT module.id, category.name AS category_name, module.name AS module_name, module.name_en AS module_name_en, module.description AS description, module.description_en AS description_en, module.content as content, module.content_en as content_en").
		Joins("JOIN safety_learning_module AS module ON item.item_id = module.id").
		Joins("JOIN safety_learning_category AS category ON module.category = category.code").
		Scan(&CommonItems).Error
	if err != nil {
		panic(err)
	}
	//equipmentDb := dao.db
	//consumableDb := dao.db
	//chemicalDb := dao.db
	//// 按分分类查对应物品
	//for _, v := range ExperimentItems {
	//	switch v.ItemType {
	//	case "1":
	//		// 查设备
	//		equipmentDb = equipmentDb.WithContext(ctx).Where("id = ?", v.ItemId)
	//	case "2":
	//		// 查耗材
	//		consumableDb = consumableDb.WithContext(ctx).Where("id = ?", v.ItemId)
	//	case "3":
	//		// 查药品
	//		chemicalDb = chemicalDb.WithContext(ctx).Where("id = ?", v.ItemId)
	//	}
	//}
	//
	//equipmentDb.Table("equipment")
	//err = equipmentDb.Debug().WithContext(ctx).Find(&equipmentItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}
	//
	//consumableDb.Table("consumable")
	//err = consumableDb.Debug().WithContext(ctx).Find(&consumableItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}
	//
	//chemicalDb.Table("chemical")
	//err = chemicalDb.Debug().WithContext(ctx).Find(&chemicalItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}
	//
	//CommonItems = append(CommonItems, equipmentItems...)
	//CommonItems = append(CommonItems, consumableItems...)
	//CommonItems = append(CommonItems, chemicalItems...)
	return CommonItems, nil
}

func (dao *ExperimentDAO) QueryNeedToLearnModule(ctx context.Context, e Experiment) ([]CommonItem, error) {
	//var ExperimentItems []ExperimentItem
	var CommonItems []CommonItem
	//var equipmentItems, consumableItems, chemicalItems []CommonItem
	db := dao.db

	var id = e.Id
	if id == "" {
		return []CommonItem{}, nil
	} else {
		db = db.WithContext(ctx).Where("item.experiment_id = ?", id)
	}

	// 查处对应物品id ，分类
	//err := db.Table("experiment_item").Debug().WithContext(ctx).Select("item_id", "item_type").Where("experiment_id = ?", id).Find(&ExperimentItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}

	// 直接查模块表
	// where 条件复用 需new 一个连接
	moduleDb := dao.db
	//err = moduleDb.Table("experiment_item").Debug().WithContext(ctx).Where("id = ?", id).Order("category").Find(&CommonItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}

	var studentId = "3456"
	//querySQl := fmt.Sprintf("select distinct module.id, category.name as 'category_name', module.name as 'module_name', score_info.score, score_info.if_pass from  experiment_item item left join safety_learning_module module on item.item_id = module.id left join safety_learning_category category on module.category = category.code join ( select es.module_id, es.score, es.if_pass from exam_score es join people p on es.people_id = p.id where es.score < 60.0 and es.if_pass = '0' and es.people_id = %v ) score_info on score_info.module_id = module.id", studentId)
	//db.Raw(querySQl).Scan(&CommonItems)
	// 构建子查询
	subSql := moduleDb.Debug().
		Table("exam_score es").
		Select("es.module_id, es.score, es.if_pass").
		Joins("JOIN people p ON es.people_id = p.id").
		Where("es.score < ?", 90).
		Or("es.if_pass = ?", 0).
		Where("es.people_id = ?", studentId)

	// 构建主查询
	err := db.Debug().
		Table("experiment_item item").
		Select("DISTINCT module.id, category.name AS category_name, module.name AS module_name, module.name_en AS module_name_en, module.description AS description, module.description_en AS description_en, module.content as content, module.content_en as content_en, score_info.score, score_info.if_pass").
		Joins("JOIN safety_learning_module module ON item.item_id = module.id").
		Joins("LEFT JOIN safety_learning_category category ON module.category = category.code").
		//Joins("LEFT JOIN question ON question.id = module.id").
		Joins("LEFT JOIN (?) score_info ON score_info.module_id = module.id", subSql).
		Order("module.id").
		Scan(&CommonItems).Error

	// 查找所有发生过事故的物品
	var itemIds []string
	newDb := dao.db
	// First query - equipment accidents
	query1 := newDb.Table("equipment_accident t1").
		Select("t1.equipment_id as item_id")

	// Second query - consumable accidents
	query2 := newDb.Table("consumable_accident t2").
		Select("t2.consumable_id as item_id")

	// Third query - chemical accidents
	query3 := newDb.Table("chemical_accident t3").
		Select("t3.chemical_id as item_id")

	// Combine with UNION ALL
	err = newDb.Debug().Raw("? UNION ALL ? UNION ALL ?", query1, query2, query3).Scan(&itemIds).Error

	if err != nil {
		return nil, fmt.Errorf("failed to query accident items: %v", err)
	}
	for k, v1 := range CommonItems {
		for _, v2 := range itemIds {
			if v1.Id == v2 {
				CommonItems[k].EverOccurredAccident = "1"
			}
		}
	}
	if err != nil {
		panic(err)
	}

	//equipmentDb := dao.db
	//consumableDb := dao.db
	//chemicalDb := dao.db
	//// 按分分类查对应物品
	//for _, v := range ExperimentItems {
	//	switch v.ItemType {
	//	case "1":
	//		// 查设备
	//		equipmentDb = equipmentDb.WithContext(ctx).Where("id = ?", v.ItemId)
	//	case "2":
	//		// 查耗材
	//		consumableDb = consumableDb.WithContext(ctx).Where("id = ?", v.ItemId)
	//	case "3":
	//		// 查药品
	//		chemicalDb = chemicalDb.WithContext(ctx).Where("id = ?", v.ItemId)
	//	}
	//}
	//
	//equipmentDb.Table("equipment")
	//err = equipmentDb.Debug().WithContext(ctx).Find(&equipmentItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}
	//
	//consumableDb.Table("consumable")
	//err = consumableDb.Debug().WithContext(ctx).Find(&consumableItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}
	//
	//chemicalDb.Table("chemical")
	//err = chemicalDb.Debug().WithContext(ctx).Find(&chemicalItems)
	//if err != nil {
	//	fmt.Println("query error")
	//}
	//
	//CommonItems = append(CommonItems, equipmentItems...)
	//CommonItems = append(CommonItems, consumableItems...)
	//CommonItems = append(CommonItems, chemicalItems...)
	return CommonItems, nil
}

type Experiment struct {
	Id            string `gorm:"autoIncrement, primary_key"`
	Name          string `gorm:"name"`
	NameEn        string `gorm:"name_en"`
	Chemicals     []string
	Equipments    []string
	Consumables   []string
	IfEnabled     int    `gorm:"if_enabled"`
	Description   string `gorm:"description"`
	DescriptionEn string `gorm:"description_en"`
}

type ExperimentItem struct {
	Id           string `gorm:"id"`
	ExperimentId string `gorm:"experiment_id"`
	ItemId       string `gorm:"item_id"`
	ItemType     string `gorm:"item_type"`
}

type CommonItem struct {
	Id                   string `gorm:"id"`
	CategoryName         string `gorm:"category_name"`
	ModuleName           string `gorm:"module_name"`
	ModuleNameEn         string `gorm:"module_name_en"`
	Score                string `gorm:"score"`
	IfPass               string `gorm:"if_pass"`
	Content              string `gorm:"content"`
	ContentEn            string `gorm:"content_en"`
	Description          string `gorm:"description"`
	DescriptionEn        string `gorm:"description_en"`
	EverOccurredAccident string `gorm:"ever_occurred_accident"`
}

type Question struct {
	Id         string `gorm:"id"`
	Content    string `gorm:"content"`
	ContentEn  string `gorm:"content_en"`
	Answer     string `gorm:"answer"`
	Analysis   string `gorm:"analysis"`
	SortNumber int    `gorm:"sort_number"`
}
